package com.ruoyi.util.excel;


import java.io.*;

import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;

public class TestMain {

	// private static SimpleDateFormat sf = null;

//	    public static void main(String[] args) {
//
//	        List<String> headerList = new ArrayList<>();
//	        Collections.addAll(headerList,"head1", "head2","head3");
//	        List<String> accountList = new ArrayList<>();
//	        Collections.addAll(accountList,"select1", "select2","select3","select4");
//	        downloadComboBoxExcel(null, headerList.toArray(new String[headerList.size()]), accountList.toArray(new String[accountList.size()]),"testExcel", 1, 500, 1, 1);
//
//	    }

	    /**
	     * 可下载指定位置设置下拉框属性，并设置表头的Excel
	     * @param response
	     * @param headers   表头内容
	     * @param selects   下拉框内容
	     * @param fileName  文件名
	     * @param firstRow  添加下拉框对应开始行
	     * @param endRow    添加下拉框对应结束行
	     * @param firstCol  添加下拉框对应开始列
	     * @param endCol    添加下拉框对应结束列
	     */
	    public static void downloadComboBoxExcel(HttpServletResponse response, String[] headers, String[] selects,
	                                             String fileName, int firstRow, int endRow, int firstCol, int endCol){
	        try {
//	            OutputStream out = response.getOutputStream();
//	            response.reset();
//	            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
//	            //application/vnd.ms-excel
//	            response.setCharacterEncoding("UTF-8");
//	            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
//	            response.setHeader("Access-Control-Allow-Origin", "*");//允许跨域访问的域
	            FileOutputStream out = new FileOutputStream("d:\\"+fileName+".xls");

	            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
	            HSSFSheet sheet = hssfWorkbook.createSheet("trackingSheet");
	            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
	            cellStyle.setAlignment(HorizontalAlignment.CENTER);
	            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	            //设置字体
	            HSSFFont font = hssfWorkbook.createFont();
	            HSSFFont font1 = hssfWorkbook.createFont();
	            font.setFontName("宋体");
	            font1.setFontName("宋体");
	            font.setFontHeightInPoints((short) 12);
	            font1.setFontHeightInPoints((short) 10);
	            font.setBold(true);
	            //设置表头边框
	            cellStyle.setBorderTop(BorderStyle.THIN);
	            cellStyle.setBorderBottom(BorderStyle.THIN);
	            cellStyle.setBorderLeft(BorderStyle.THIN);
	            cellStyle.setBorderRight(BorderStyle.THIN);
	            //设置边距
	            sheet.setDefaultColumnWidth(18);
	            sheet.setDefaultRowHeightInPoints(20);

	            //新增数据行，并且设置单元格数据
	            int rowNum = 0;
	            //在表中存放查询到的数据放入对应的列
	            //在excel表中添加表头
	            HSSFRow row = sheet.createRow(rowNum);
	            row.setHeightInPoints(23);
	            for (int j = 0; j < headers.length; j++) {
	                HSSFCell cell = row.createCell(j);
	                cell.setCellValue(headers[j]);
	                cell.setCellStyle(cellStyle);
	            }
	            //设置下拉框
	            setHSSFValidation(sheet, selects, firstRow, endRow, firstCol, endCol);
	            hssfWorkbook.write(out);
	            out.flush();
	            out.close();
	        }catch(Exception e){
	            e.printStackTrace();
	        }
	    }

	    /**
	     *设置某些列的值只能输入预制的数据,显示下拉框
	     * @param sheet     HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
	     * @param textlist  下拉框内容
	     * @param firstRow  添加下拉框对应开始行
	     * @param endRow    添加下拉框对应结束行
	     * @param firstCol  添加下拉框对应开始列
	     * @param endCol    添加下拉框对应结束列
	     * @return
	     */
	    private static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
	        //这里将下拉框进行拆分存储在每一个单元格  并进行隐藏  防止下拉框数据过多存储在一个单元格的字符数大于255而报错
	        HSSFWorkbook workbook = sheet.getWorkbook();
	        Sheet hidden = workbook.createSheet("hidden");
	        Cell cell = null;
	        for (int i = 0, length = textlist.length; i < length; i++)
	        {
	            String name = textlist[i];
	            Row roww = hidden.createRow(i);
	            cell = roww.createCell(0);
	            cell.setCellValue(name);
	        }

	        Name namedCell = workbook.createName();
	        namedCell.setNameName("hidden");
	        namedCell.setRefersToFormula("hidden!$A$1:$A$" + textlist.length);
	        //将第二个sheet页设置为隐藏
	        workbook.setSheetHidden(1, true);
	        //将名称为hidden的数据进行加载
	        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
	        //设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
	        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
	        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
	        sheet.addValidationData(data_validation_list);
	        return sheet;
	    }
	
	
	
}
